#encoding: utf-8
=begin
Usage:      This ruby file will merge pospal users data xlsx (data/a.xlsx) with crmeb's (data/b.xlsx)
=end

load 'rds_api.rb'
load 'crmeb_utils.rb'

def read_user_excel type, xlsx

  @user_key = {} if @user_key.nil? 
  @user_info = {} if @user_info.nil? 
  @user_pospal = {} if @user_pospal.nil? 
  @user_crmeb = {} if @user_crmeb.nil? 

  s = SimpleSpreadsheet::Workbook.read xlsx
  s.selected_sheet = s.sheets.first
  line_idx = 0
  s.first_row.upto(s.last_row) do |line|
      line_idx += 1
      next if line_idx == 1
      unionid = s.cell(line,3).to_s
      @user_key.store(unionid,unionid)
      data = {
        "tel" =>  s.cell(line,4).to_s,
        "name" => s.cell(line,5).to_s,
        "addr" => s.cell(line,6).to_s
      }
      @user_info.store(s.cell(line,3),data)
      if type == :POSPAL 
        data = {
          "pospal_order_times" =>  s.cell(line,1).to_i,
          "pospal_total_amount" => s.cell(line,2).to_f
        }
        @user_pospal.store(unionid,data)
      else
        data = {
          "crmeb_order_times" => s.cell(line,1).to_i,
          "crmeb_total_amount" => s.cell(line,2).to_f
        }
        @user_crmeb.store(unionid,data)
      end

  end
  puts "\ndone. #{line_idx}"

end

def excel_ab_to_users
  fa =  OS.windows? ? ".\\data\\a.xlsx" : "./data/a.xlsx"
  fb =  OS.windows? ? ".\\data\\b.xlsx" : "./data/b.xlsx"
  read_user_excel :POSPAL, fa
  read_user_excel :CRMEB, fb
  merged = []
  @user_key.each do |key, data|
    pot = @user_pospal[key].nil? ?  0 : @user_pospal[key]['pospal_order_times']
    pta = @user_pospal[key].nil? ? 0 : @user_pospal[key]['pospal_total_amount']
    cot = @user_crmeb[key].nil? ? 0 : @user_crmeb[key]['crmeb_order_times']
    cta = @user_crmeb[key].nil? ? 0 : @user_crmeb[key]['crmeb_total_amount']
    begin #to skip blank line
      new_data = {
        "unionid" => key,
        "times" => pot + cot,
        "amount" =>  pta + cta,
        "pospal_order_times" => pot,
        "pospal_total_amount" => pta,
        "crmeb_order_times" =>  cot,
        "crmeb_total_amount" => cta,
        "tel" =>  @user_info[key]['tel'],
        "name" =>  @user_info[key]['name'],
        "addr" =>  @user_info[key]['addr']
      }
      merged += [new_data]
    rescue => e
    end
  end
  return merged
end

merged = excel_ab_to_users
ap merged
headers = ['unionid', "times", "amount", "pospal_order_times", "pospal_total_amount", "crmeb_order_times", "crmeb_total_amount", "tel", "name", "addr"]
names = ['unionid', "times", "amount", "pospal_order_times", "pospal_total_amount", "crmeb_order_times", "crmeb_total_amount", "tel", "name", "addr"]
fn =  OS.windows? ? ".\\data\\merged.xls" : "./data/merged.xls"
save_to_excel headers, names, merged, fn
